import os
import pyodbc
import pandas as pd
import Get_HTMLtables as GH
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook

global tables
import pyodbc
import re
import os


class PDFTableExtractor:
    def __init__(self, ID, find_keyword, Html_file):
        self.ID = ID
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
        self.ZBDW = read_html_obj.read_DW(Html_file, find_keyword)

    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                   SELECT DISTINCT EPBH
                   FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                   WHERE QYBH= ?
                       """
        IGSDM = str(self.IGSDM)
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'

            return self.EPBH

    def get_GG(self):
        server = 'SHPUBSRV01'
        database = 'JYPRIME'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        ID = self.ID
        query = """
                                  SELECT ID,IGSDM,CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ,CONVERT(varchar(10),JZRQ,23) AS JZRQ,XXBT,GGLJ
                                  FROM usrGSGGYWFWB GG 
                                  WHERE ID= CAST(? AS NVARCHAR)
                                      """
        self.GG = pd.read_sql(query, conn, params=[ID])
        if not self.GG.empty:
            self.IGSDM = self.GG['IGSDM'][0]
            self.XXFBRQ = self.GG['XXFBRQ'][0]
            self.JZRQ = self.GG['JZRQ'][0]
            self.XXLY = self.GG['XXBT'][0]

            return self.GG

    def find_keyword_and_extract_table(self, Html_file, find_keyword):
        # 读取HTML文件，提取表格
        table = GH.ReadHtml.read_html_file(Html_file, find_keyword)
        # 删除指定行和列\
        if table.shape[0] != 0:
            all_yysr = table.iloc[0:3]
            for index, row in all_yysr.iterrows():  # 使用 iterrows() 方法遍历 DataFrame 的行
                if '营业收入合计' in row.values:  # 使用 row.values 来获取行数据的数组，并判断目标字符串是否在其中
                    all_yysr = row  # 如果包含目标字符串，则将该行赋值给 all_yysr
                    break  # 找到目标字符串后退出循环

            table = table.drop([0, 1, 2], axis=0).drop([2, 3, 4], axis=1)
            # 重置索引
            table = table.reset_index(drop=True)
            # 要查找的关键字列表
            keywords = ["国内", "国外"]
            index_domestic = None
            index_foreign = None
        # 输出关键字的位置
        try:
            for keyword in keywords:
                for index, row in table.iterrows():
                    if keyword in row[0]:
                        if keyword == "国内":
                            index_domestic = index
                        elif keyword == "国外":
                            index_foreign = index
                        break  # 找到关键字后，停止查找该关键字的行

            if index_domestic and index_foreign:
                table[0].iloc[index_domestic + 1:index_foreign] = '国内:' + table[0].iloc[index_domestic + 1:index_foreign]
            table = table.replace('国外销售：', '国外').replace('其中：', '', regex=True).replace('国内销售：', '国内').replace('分行业/分产品','分行业')
        except:
            print(Exception)
        # 返回修改后的表格
        return table

    # 切割表格(营收)
    def sp(self, table):
        keywords = ['分行业', '分产品', '分地区', '分销售模式']
        print('开始分割')
        if any(table.isin(['分行业', '分产品', '分地区', '分销售模式'])):
            new_df = table
            new_df.columns = range(len(new_df.columns))
            BASCI_split_indices = [0]
            new_df[0] = new_df[0].astype(str)
            split_indices = new_df[
                new_df[0].apply(lambda x: any(keyword in x for keyword in keywords))].index.tolist()
            split_indices = [int(str(index).replace('[', '').replace(']', '')) for index in split_indices]
            split_indices = BASCI_split_indices + split_indices
            if split_indices:
                split_indices.append(len(new_df))
                with pd.ExcelWriter('分割表格.xlsx', engine='xlsxwriter') as writer:
                    for i in range(0, len(split_indices)):
                        if split_indices[i] > 0:
                            dfs = new_df.iloc[split_indices[i - 1]:split_indices[i]]
                            sheet_name = dfs[0].iloc[0]
                            dfs.to_excel(writer, sheet_name=sheet_name, index=False)
            else:
                print("No split indices found.")

    # 获取代码类字段(营收)
    def get_code(self):
        db_name = r'\\10.3.2.15\固收部\其他\经营指标\自动化标准化存档\mydatabase.db'
        con = connect(db_name)
        Product = "SELECT * FROM product_code"
        self.PRODUCT_CODE = pd.read_sql(Product, con)
        ZB = 'SELECT * FROM ZB_CODE'
        self.ZB_CODE = pd.read_sql(ZB, con)
        DQ = 'SELECT * FROM city_code'
        self.DQ_CODE = pd.read_sql(DQ, con)
        BT = 'SELECT * FROM GG  '
        self.BT = pd.read_sql(BT, con)
        BZH = 'SELECT * FROM BZH  WHERE EPBH =?'
        self.BZH = pd.read_sql(BZH, con, params=[self.IGSDM])
        con.close()

    # 格式化为企业经营的底层表(营收)
    def xggs(self):
        all_merged_data = pd.DataFrame()
        try:
            excel_file_path = '分割表格.xlsx'
            new_excel_file_path = f'{self.EPBH}_{self.JZRQ}_营收表_全量.xlsx'
            workbook = openpyxl.load_workbook(excel_file_path)
            sheet_names = workbook.sheetnames
            with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
                for i, sheet_name in enumerate(sheet_names, start=1):
                    print(f'Processing sheet {i}')
                    yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ', '', regex=True)
                    prev_index = None
                    for index, row in yysr.iterrows():
                        n = 0
                        for cow in row:
                            if pd.isna(cow):
                                n = n + 1
                                if n == 3:
                                    a = yysr.iloc[index - 1]
                                    a = a.fillna('')
                                    b = yysr.iloc[index]
                                    b = b.fillna('')
                                    combined_row = a + b
                                    yysr.iloc[index - 1] = combined_row
                                    yysr.drop(index, inplace=True)
                                    break
                            else:
                                n = 0
                            prev_index = index
                    if '分产品' in sheet_names and '分行业' in sheet_names and sheet_name == '分行业':
                        continue
                    if sheet_name == '分产品':
                        new_columns = {0: 'SJLMYMC', 1: '销售收入', 2: '销售收入同比'}
                    elif sheet_name == '分地区':
                        new_columns = {0: 'SJLMEMC', 1: '销售收入', 2: '销售收入同比'}
                    elif sheet_name == '分销售模式':
                        new_columns = {0: 'SJLMSMC', 1: '销售收入', 2: '销售收入同比'}
                    elif sheet_name == '分行业':
                        new_columns={0:'SJLMYMC',1:'销售收入',2:'销售收入同比'}
                    id_vars = yysr[yysr.columns[0]][0].replace('分产品', 'SJLMYMC').replace('分地区', 'SJLMEMC').replace(
                        '分销售模式', 'SJLMSMC').replace('分行业', 'SJLMYMC').replace('分产品或服务', 'SJLMYMC')
                    yysr = yysr.rename(columns=new_columns)
                    yysr = yysr.drop(0)
                    yysr = pd.melt(yysr,
                                   id_vars=id_vars,
                                   value_vars=list(yysr.columns)[1:],
                                   var_name='ZBMC',
                                   value_name='ZBSJ')

                    yysr['JZRQ'] = None
                    yysr['ZTYSMC'] = None
                    yysr['SJLMY'] = None
                    yysr['EPBH'] = None
                    yysr['XXFBRQ'] = None
                    yysr['ZBDW'] = None
                    yysr['SJLME'] = None
                    yysr['SJLMS'] = None
                    yysr['TJKJ'] = None
                    yysr['SFYX'] = None
                    yysr['SFYX'].iloc[:len(yysr)] = 'FCC000000006'
                    conditions = [
                        yysr['ZBMC'] == '销售收入',
                        yysr['ZBMC'] == '销售收入同比']
                    values = ['累计值', '累计同比增长率']
                    yysr['TJKJ'] = np.select(conditions, values)
                    DW = [
                        yysr['ZBMC'] == '销售收入',
                        yysr['ZBMC'] == '销售收入同比']
                    DW_VALUES = [self.ZBDW, '%']
                    yysr['ZBDW'] = np.select(DW, DW_VALUES)
                    yysr['TJQJ'] = '半年'
                    yysr['JYYWLXDM'] = None
                    yysr['XXLY'] = None
                    yysr['ZBMC'] = yysr['ZBMC'].replace('（%）', '', regex=True).replace('比上年增减', '', regex=True). \
                        replace('营业收入', '销售收入', regex=True).astype(str).replace('(%)', '', regex=True)
                    yysr['ZBSJ'] = yysr['ZBSJ'].replace('减少', '-', regex=True).replace('增加', '', regex=True) \
                        .replace('个百分点', '', regex=True).replace('不适用', '', regex=True).replace('/', '', regex=True) \
                        .replace('%', '', regex=True)
                    if id_vars == 'SJLMYMC':
                        yysr['SJLMYDM'] = yysr.merge(self.PRODUCT_CODE, on='SJLMYMC', how='left')['标准代码']
                        yysr['SJLMY'].iloc[:len(yysr)] = '产品'
                        yysr['JYYWLXDM'].iloc[:len(yysr)] = '销售情况（按产品类型）'
                        yysr['SJLMYMC'] = yysr['SJLMYMC'].replace('合计', '产品合计', regex=True)
                    elif id_vars == 'SJLMEMC':
                        yysr['SJLME'].iloc[:len(yysr)] = '地区'
                        yysr['JYYWLXDM'].iloc[:len(yysr)] = '销售情况（按区域）'
                        yysr['SJLMEMC'] = yysr['SJLMEMC'].replace('合计', '地区合计', regex=True)
                        yysr['SJLMEDM'] = yysr.merge(self.DQ_CODE, on='SJLMEMC', how='left')['DM']
                    elif id_vars == 'SJLMSMC':
                        yysr['SJLMS'].iloc[:len(yysr)] = '销售模式'
                        yysr['JYYWLXDM'].iloc[:len(yysr)] = '销售情况（按销售渠道）'
                    yysr['ZBMC'] = yysr['ZBMC'].replace('销售收入同比', '销售收入')
                    yysr['ZBDM'] = yysr.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
                    yysr['EPBH'].iloc[:len(yysr)] = str(self.EPBH)
                    yysr['JZRQ'].iloc[:len(yysr)] = self.JZRQ
                    yysr['XXLY'].iloc[:len(yysr)] = self.XXLY
                    yysr['XXFBRQ'].iloc[:len(yysr)] = self.XXFBRQ
                    yysr = yysr.reindex(
                        columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
                                 'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
                                 'ZBDW', 'TJKJ', 'TJQJ'])
                    yysr = yysr.replace(' ', '')

                    for inde, row in self.BZH.iterrows():
                        yysr = yysr.replace(row['BBZHMC'], row['BZHMC'])
                    all_merged_data = pd.concat([all_merged_data, yysr])
                all_merged_data.to_excel(writer, index=False, sheet_name='营收表')
        except:
            print(f'袁凯锋：{Exception}')
        return all_merged_data


def run_all(ID, Html_file):
    Html_file = Html_file
    find_keyword = [['占营业收入比重', '分行业'], ['占营业总收入比重', '分产品'],['营业收入比上年同期增减','分产品']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword, Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    extractor_instance.get_code()
    extractor_instance.sp(table)
    if table.shape[1] != 0:
        table = extractor_instance.xggs()
        return table
    else:
        print('未找到关键词')
    return pd.DataFrame()


if __name__ == '__main__':
    ID = '731708111313'
    Html_file = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\731708111313.HTML'
    run_all(ID, Html_file)
